//===============================================================================
// Microsoft Architecture Strategy Team
// LitwareHR - SaaS Sample Application
//===============================================================================
// Copyright  Microsoft Corporation.  All rights reserved.
// THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY
// OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT
// LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND
// FITNESS FOR A PARTICULAR PURPOSE.
//===============================================================================
// The example companies, organizations, products, domain names,
// e-mail addresses, logos, people, places, and events depicted
// herein are fictitious.  No association with any real company,
// organization, product, domain name, email address, logo, person,
// places, or events is intended or should be inferred.
//===============================================================================


using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using Microsoft.Office.Interop.Excel;
using System.Reflection;

namespace LitwareHR.PublicApp.WinUX.ExcelImport
{
    public class ExcelImport
    {
        public static List<String[]> Import(string fileName)
        {
            if (!File.Exists(fileName))
            {
                return null;
            }

            object[,] values;
            ReadExcel(fileName, out values);

            //Check for some data to import
            if (values.GetUpperBound(0) - values.GetLowerBound(0) == 0)
            {
                return null;
            }

            List<String[]> valueList = new List<String[]>();

            for (int i = values.GetLowerBound(0); i <= values.GetUpperBound(0); i++)
            {
                List<String> data = new List<String>();
                for (int j = values.GetLowerBound(1); j <= values.GetUpperBound(1); j++)
                {
                    data.Add(values[i, j] == null ? string.Empty : values[i, j].ToString());
                }
                
                valueList.Add(data.ToArray());
            }

            return valueList;
        }

        private static void ReadExcel(string fileName, out object[,] values)
        {
            ApplicationClass app = new ApplicationClass(); // the Excel application.
            Workbook book = null;
            Worksheet sheet = null;
            Range range = null;

            app.Visible = false;
            app.ScreenUpdating = false;
            app.DisplayAlerts = false;

            book = app.Workbooks.Open(fileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            sheet = (Worksheet)book.Worksheets[1];

            String strUpAddress = "A1";
            range = sheet.get_Range(strUpAddress, Missing.Value);
            // Move to right most non empty cell 
            range = range.get_End(XlDirection.xlToRight);
            // Move to bottom most non empty cell
            range = range.get_End(XlDirection.xlDown);
            // Get the bottom right cell
            string strDownAddress = range.get_Address(false, false, XlReferenceStyle.xlA1, Type.Missing, Type.Missing);
            range = sheet.get_Range(strUpAddress, strDownAddress);

            // Reading the data
            values = (object[,])range.Cells.Value2;

            //Clean UP Excel Objects
            range = null;
            sheet = null;

            if (book != null)
                book.Close(false, Missing.Value, Missing.Value);

            book = null;

            if (app != null)
                app.Quit();

            app = null;
        }
    }
}
